using System;
using System.Data;
using System.Data.SqlClient;
using System.Configuration;
using System.Collections;
using System.Web;
using System.Web.Security;
using System.Web.UI;
using System.Web.UI.WebControls;
using System.Web.UI.WebControls.WebParts;
using System.Web.UI.HtmlControls;
using labDict.Web.provider;
using labDict.Web.business;
using labDict.Web.common;

namespace labDict.Web
{
    public partial class ViewDataTable : System.Web.UI.Page
    {
        int dataTableType = -1;
        string tableName = "";
        int tableid = -1;
        public static DataTable Table1 = new DataTable();
        ArrayList ParameterArray = new ArrayList();

        public enum DataField
        {
            TableName = 0,
            TableID = 1
        

        }

        protected void Page_Load(object sender, EventArgs e)
        {
            if (!IsPostBack)
            {
                //Go edit table
                if (Request.QueryString["id"] != null && Request.QueryString["id"] != "")
                {
                    tableid = Int32.Parse(Request.QueryString["id"]);
                    dataTableType = tableid;
                    hfTableID.Value = tableid.ToString();
                    tableName = DataTables.GetTableName(dataTableType);
                }
            }

            if (dataTableType < 0)
            {
                if (hfTableID.Value != "")
                    tableName = DataTables.GetTableName(Int32.Parse(hfTableID.Value));
                else
                    tableName = DataTables.GetTableName(dataTableType);
            }

            CreateTemplatedGridView();

            gvDataTable.DataBind();
            //BuildAddNewTable();
            
            lblMessage.Text = "Data table: " + dataTableType;
            mvDataTables.SetActiveView(vEditTable);

            dataTableType = (int)DataTables.DataTableType.Container;
        }

        public void gvDataTable_RowEditing(object sender, GridViewEditEventArgs e)
        {
            gvDataTable.EditIndex = e.NewEditIndex;
            gvDataTable.DataBind();
            //Session["SelecetdRowIndex"] = e.NewEditIndex;
        }

        public void gvDataTable_CancelingEditing(object sender, GridViewCancelEditEventArgs e)
        {
            gvDataTable.EditIndex = -1;
            gvDataTable.DataBind();
            //Session["SelecetdRowIndex"] = e.NewEditIndex;
        }

        public void gvDataTable_RowUpdating(Object sender, GridViewUpdateEventArgs e)
        {
            GridViewRow row = gvDataTable.Rows[e.RowIndex];

            for (int i = 0; i < Table1.Columns.Count; i++)
            {
                string colValue = "";

                if (i == 0) //this is the index 
                {
                    Label lbl1 = (Label)row.FindControl(Table1.Columns[i].ColumnName);
                    colValue = (lbl1 != null && lbl1.Text != "") ? lbl1.Text : "";
                }
                else {
                   TextBox tb1 = (TextBox)row.FindControl(Table1.Columns[i].ColumnName);
                   colValue = (tb1 != null && tb1.Text != "") ? tb1.Text : "";
                }

                if (colValue != "")
                {
                    ParameterArray.Add(colValue);
                }
            }
            string Query = "";
            if ((int)Session["InsertFlag"] == 1)
                Query = GenerateInsertQuery();
            else
                Query = GenerateUpdateQuery();

            try
            {
                DataTables.UpdateDataTable(Query);
                Session["InsertFlag"] = (int)Session["InsertFlag"] == 1 ? 0 : 1;

            }
            catch (SqlException se)
            {
                lblMessage.Text = se.ToString();
            }

            
            CreateTemplatedGridView();
            gvDataTable.EditIndex = -1;
            gvDataTable.DataBind();
            
        }

        void PopulateDataTable()
        {
            Table1 = new DataTable();
            gvDataTable.Columns.Clear();

            try
            {
                Table1 = SQLHelper.GetResult("Select * from " + tableName);
            }
            catch (Exception ex)
            {
                lblMessage.Text = ex.ToString();
            }
        }

        protected string DisplayData(DataListItem dliItem, DataField pColumn)
        {
            string strData = null;

            if (dliItem.ItemType != ListItemType.Item
                && dliItem.ItemType != ListItemType.AlternatingItem)
            {
                return null;
            }

            try
            {
                switch (pColumn)
                {
                    case DataField.TableName:
                        strData = "";// _category.Description.Value;
                        break;
                    case DataField.TableID:
                        strData = "";// "cat" + _category.IconIndex.ToString();
                        break;
                    default:
                        strData = "";
                        break;
                }
            }
            catch (Exception e)
            {
                strData = e.Message;
            }

            return strData;
        }

        protected void dlAllTables_ItemCreated(object sender, DataListItemEventArgs e)
        {
            if (e.Item.ItemType != ListItemType.Item && e.Item.ItemType != ListItemType.AlternatingItem)
                return;

           //
        }

        protected void dlAllTables_ItemCommand(object source, DataListCommandEventArgs e)
        {
            //;
        }


        void CreateTemplatedGridView()
        {
            // fill the table which is to bound to the GridView
            PopulateDataTable();
            // add templated fields to the GridView
            TemplateField BtnTmpField = new TemplateField();
            BtnTmpField.ItemTemplate =
                new TemplatedGridViewHandler(ListItemType.Item, "...", "Command");
            BtnTmpField.HeaderTemplate =
                new TemplatedGridViewHandler(ListItemType.Header, "...", "Command");
            BtnTmpField.EditItemTemplate =
                new TemplatedGridViewHandler(ListItemType.EditItem, "...", "Command");
            BtnTmpField.FooterTemplate =
                new TemplatedGridViewHandler(ListItemType.Footer, "...", "Command", Page);

            
            gvDataTable.Columns.Add(BtnTmpField);

            for (int i = 0; i < Table1.Columns.Count; i++)
            {
                TemplateField ItemTmpField = new TemplateField();
                // create HeaderTemplate
                ItemTmpField.HeaderTemplate = new TemplatedGridViewHandler(ListItemType.Header,
                                                              Table1.Columns[i].ColumnName,
                                                              Table1.Columns[i].DataType.Name);
                // create ItemTemplate
                ItemTmpField.ItemTemplate = new TemplatedGridViewHandler(ListItemType.Item,
                                                              Table1.Columns[i].ColumnName,
                                                              Table1.Columns[i].DataType.Name);

                if (i != 0)
                {
                    //create EditItemTemplate
                    ItemTmpField.EditItemTemplate = new TemplatedGridViewHandler(ListItemType.EditItem,
                                                                  Table1.Columns[i].ColumnName,
                                                                  Table1.Columns[i].DataType.Name);
                    //create EditItemTemplate
                    ItemTmpField.FooterTemplate = new TemplatedGridViewHandler(ListItemType.Footer,
                                                                  Table1.Columns[i].ColumnName,
                                                                  Table1.Columns[i].DataType.Name);
                }

                // then add to the GridView
                gvDataTable.Columns.Add(ItemTmpField);

            }
            
            // bind and display the data
            gvDataTable.DataSource = Table1;
        }
   

        string GenerateUpdateQuery()
        {
            int i = 0;
            string tempstr = "";
            int temp_index = -1;

            string Query = "";
            Query = "Update  " + tableName + " set ";

            for (i = 1; i < Table1.Columns.Count; i++)
            {
                switch (Table1.Columns[i].DataType.Name)
                {

                    case "Boolean":
                    case "Int32":
                    case "Byte":
                    case "Decimal":
                        if ((string)ParameterArray[i] == "True")
                            ParameterArray[i] = "1";
                        else if ((string)ParameterArray[i] == "False")
                            ParameterArray[i] = "0";
                        
                        if (i == Table1.Columns.Count - 1)
                            Query = Query + Table1.Columns[i].ColumnName + "=" + ParameterArray[i];
                        else
                            Query = Query + Table1.Columns[i].ColumnName + "=" + ParameterArray[i] + ", ";

                        break;
                    case "String":
                    case "DateTime":
                        if (((string)ParameterArray[i]).Contains("'"))
                        {
                            tempstr = ((string)ParameterArray[i]);
                            ParameterArray[i] = ((string)ParameterArray[i]).Replace("'", "''");
                            temp_index = i;
                        }

                        if (i == Table1.Columns.Count - 1)
                            Query = Query + Table1.Columns[i].ColumnName + "='" + ParameterArray[i] + "' ";
                        else
                            Query = Query + Table1.Columns[i].ColumnName + "='" + ParameterArray[i] + "', ";
                        break;

                }
            }
            if (temp_index > -1)
                ParameterArray[temp_index] = tempstr;
            if (Table1.Columns[0].DataType.Name == "String" || Table1.Columns[0].DataType.Name == "DateTime")
                Query = Query + " where " + Table1.Columns[0].ColumnName + " = '" + ParameterArray[0] + "'";
            else
                Query = Query + " where " + Table1.Columns[0].ColumnName + " = " + ParameterArray[0];

            return Query;
        }

        private void BuildAddNewTable()
        { 
            Table t = new Table();
            TableRow tr = new TableRow();
            TableCell tbc1 = new TableCell();

            //Build Add LinkButton
            LinkButton lbAddNew = new LinkButton();
            lbAddNew.Text = "add";
            lbAddNew.Click += new EventHandler(lbAddNew_Click);
            tbc1.Controls.Add(lbAddNew);

            tr.Cells.Add(tbc1);

            for (int i = 0; i < Table1.Columns.Count; i++)
            {
                TableCell tbCell = new TableCell();
                TextBox tb = new TextBox();
                tb.ID = "New" + Table1.Columns[i].ColumnName;
                tb.Width = new Unit("200");
                tbCell.Controls.Add(tb);
                tr.Cells.Add(tbCell);
            }

            tblAddNew.Rows.Add(tr);
        }

        //just sets the insert flag ON so that we ll be able to decide in OnRowUpdating event whether to insert or update
        public void lbAddNew_Click(Object sender, EventArgs e)
        {
            ParameterArray.Clear();

            LinkButton lb = (LinkButton)sender;
            string id = lb.NamingContainer.ID;

            Control con = lb.NamingContainer;

            for (int i = 0; i < Table1.Columns.Count; i++)
            {
                TextBox tb1 = (TextBox)con.FindControl("New" + Table1.Columns[i].ColumnName);
                string colValue = (tb1 != null && tb1.Text != "") ? tb1.Text : "";

                ParameterArray.Add(colValue);
            }

            string Query = GenerateInsertQuery();
            
            try
            {
                DataTables.InsertData(Query);
            }
            catch (SqlException se)
            {
                lblMessage.Text = se.ToString();
            }
            CreateTemplatedGridView();
            gvDataTable.EditIndex = -1;
            gvDataTable.DataBind();
            
        }

        string GenerateInsertQuery()
        {
            int i = 0;
            string tempstr = "";
            int temp_index = -1;
            int new_index = DataTables.GetNewIndex(tableName, Table1.Columns[0].ColumnName);
                        
            string Query = "";
            Query = "Insert into  " + tableName + "(";

            for (i = 0; i < Table1.Columns.Count; i++)
            {
                if (i == Table1.Columns.Count - 1)
                    Query = Query + Table1.Columns[i].ColumnName;
                else
                    Query = Query + Table1.Columns[i].ColumnName + ", ";

            }

            Query = Query + ")" + "Values (";
            Query += new_index + ",";
            
            for (i = 1; i < Table1.Columns.Count; i++)
            {

                switch (Table1.Columns[i].DataType.Name)
                {

                    case "Boolean":
                    case "Int32":
                    case "Byte":
                    case "Decimal":
                        if ((string)ParameterArray[i] == "True")
                            ParameterArray[i] = "1";
                        else if ((string)ParameterArray[i] == "False")
                            ParameterArray[i] = "0";

                        if (i == Table1.Columns.Count - 1)
                            Query = Query + ParameterArray[i];
                        else
                            Query = Query + ParameterArray[i] + ", ";

                        break;
                    case "String":
                    case "DateTime":
                        if (((string)ParameterArray[i]).Contains("'"))
                        {
                            tempstr = ((string)ParameterArray[i]);
                            ParameterArray[i] = ((string)ParameterArray[i]).Replace("'", "''");
                            temp_index = i;
                        }
                        if (i == Table1.Columns.Count - 1)
                            Query = Query + "'" + ParameterArray[i] + "' ";
                        else
                            Query = Query + "'" + ParameterArray[i] + "', ";
                        break;

                }


            }
            Query = Query + ")";


            return Query;
        }
    }
}
